<?php
function printYear($date)
{
	return "<td class=\"EventReportYear\" colspan=\"4\">".$date->format("Y")."</td></tr><tr>";
}

function printMonth($date)
{
	return "<td class=\"EventReportMonth\" colspan=\"4\">".$date->format("F")."</td></tr><tr>";
}

function printDay($date)
{
	return "<td class=\"EventReportDay\" colspan=\"4\">".$date->format("j l")."</td></tr><tr>";
}


// echo dirname(__FILE__)."\n";

$topdirectory=dirname(__FILE__)."/../../MSWebGUI";
include $topdirectory.'/FileList.php';

$topdirectory=dirname(__FILE__)."/..";
include $topdirectory.'/FileList.php';


$xml = simplexml_load_file(dirname(__FILE__).'/../config/config.xml');
$databaseXML = $xml->Database;
$database = new Database($databaseXML->Server,$databaseXML->Database,$databaseXML->Username,$databaseXML->Password);

$dtWas = new DateTime('-1 week -1 day');
$dtNow = new DateTime('now');
$dtThen = new DateTime('+1 month');
$valueWas = $dtWas->format("Y-m-d H:i");
$valueNow = $dtNow->format("Y-m-d H:i");
$valueThen = $dtThen->format("Y-m-d H:i");
$sql = "SELECT * FROM Events WHERE Start > '".$valueWas."' AND Start < '".$valueThen."' AND DeletedBy = '0' ORDER BY Start";
//echo $sql."\n";
$database->ExecuteSQL($sql);
$dtThen->modify('-1 day');
$messageHTML = "";
$messageHTML .= "<h1>The Month Ahead (and week behind)</h1>\n";
$messageHTML .= "<p>".$dtWas->format("F j, Y")." - ".$dtThen->format("F j, Y")."</p>\n";
$messageHTML .= "<p>(excludes all choir only events)</p>\n";

// get Locations
$locations = "";
$sql = "SELECT * FROM LocationNames";
$database1 = $database->Recopy();
$database1->ExecuteSQL($sql);
//	echo $sql."<br>\n";
$messageHTML .= "<table class=\"locationsList\">";
//$messageHTML .= "<p style=\"font-weight: 100;\">";
while($row1 = $database1->getRow())
{
	$messageHTML .= "<tr class=\"locationsList\"><td width=\"30px\">&nbsp</td><td class=\"locationsList\">".$row1["ShortName"]."</td><td class=\"locationsList\">".$row1["LongName"]."</td></tr>\n";
}
$messageHTML .= "</table><br><br>";	



$messageHTML .= "<table>\n";

$previousDate = new DateTime("-10 years");

$users= new Enumeration($database,'SnapUsers');
$array = $users->getArray();
//print_r($array);

while($row = $database->getRow())
{
	
// exclude choir only events
	$sql = "SELECT EventInvolveeNameID FROM EventInvolvees WHERE EventID = ".$row['EventID'];
	$database1 = $database->Recopy();
	$database1->ExecuteSQL($sql);
	$result = array();
	while($row1 = $database1->getRow())
	{
		$result[] = $row1['EventInvolveeNameID'];
	}
	
//	echo $row['Title']."  -  ".count($result)."<br>\n";
	
	if(count($result)!=0) // if no involvees we need to include it
	{
		$dontskip = false;
		foreach($result as $involvee)
		{
			if(($involvee != 1)&&($involvee != 2)&&($involvee != 3)&&($involvee != 4)&&($involvee != 5))
			{
//				echo "don't skip for involvee ".$involvee."<br>\n";
				$dontskip = true; // if not a choir involvee we need to include - otherwise skip
				break;
			}
		}
		if(!$dontskip)continue;
	}
	
	
	$start = new datetime($row['Start']);
	if($start->format("Y")!=$previousDate->format("Y"))$messageHTML .= printYear($start);
	if($start->format("Ym")!=$previousDate->format("Ym"))$messageHTML .= printMonth($start);
	if($start->format("Ymd")!=$previousDate->format("Ymd"))$messageHTML .= printDay($start);
	$previousDate = $start;
	
	// get Locations
	
	$locations = "";
	$sql = "SELECT id.LocationNameID, n.ShortName FROM Locations as id INNER JOIN LocationNames as n ON id.LocationNameID = n.LocationNameID WHERE id.EventID = ".$row['EventID'];
	$database1 = $database->Recopy();
	$database1->ExecuteSQL($sql);
//	echo $sql."<br>\n";
	while($row1 = $database1->getRow())
	{
		$locations .= $row1["ShortName"]."&nbsp;";
	}
	
	$startString = $start->format("H:i");
//	$messageHTML .= "<tr><td width=\"20px\"></td><td".$style.">".$startString."</td><td".$style.">".$resultRow['Title']."</td><td".$style.">".$locations."</td><td".$style.">".$resultRow['Description']."</td><td".$style.">".$text."&nbsp;".$strDate."</td></tr>\n";
	$messageHTML .= "<tr><td width=\"20px\"></td><td>".$startString."</td><td>".$row['Title']."</td><td>".$locations."</td><td>".$row['Description']."</td></tr>\n";
}
$messageHTML .= "</table>\n";
//echo $messageHTML."<br>";

$message = new Message();

$sql = "SELECT Email FROM SnapUsers";
$database->ExecuteSQL($sql);

while($row = $database->getRow())
{
	if($row['Email']!="")$message->addRecipient($row['Email']);
}

//$message->addRecipient("michael@michaelstoddart.org");
//$message->addRecipient("directorofmusic@stnicnewcastle.co.uk");
$message->setSubject("The Month Ahead and week behind - ".$dtNow->format("F j, Y"));
$message->setFrom("St Nicholas Activity Planning<no-reply@stnicholascathedralmusic.co.uk>");
$message->addStyle("p, td {font-family: sans-serif;vertical-align:top;font-size: 70%;color:#003366;}");
$message->addStyle("h1{font-family: sans-serif;font-size: 100%;color:#003366;}");
$message->addStyle("h2{font-family: sans-serif;font-size: 80%;color:#003366;}");
$message->addStyle("td.EventReportYear{font-family: sans-serif;font-size: 120%;font-weight: 400; color:#003366;}");
$message->addStyle("td.EventReportMonth{font-family: sans-serif;font-size: 110%;font-weight: 400; color:#003366;}");
$message->addStyle("td.EventReportDay{font-family: sans-serif;font-size: 80%;font-weight: 400; color:#003366;}");
$message->addStyle("table.locationsList {color: #332233; font-weight: 100; padding: 0px; margin: 0px;}");
$message->addStyle("tr.locationsList {color: #332233; font-weight: 100; padding: 0px; margin: 0px;}");
$message->addStyle("td.locationsList {color: #332233; font-weight: 100; padding: 0px; margin: 0px;}");

$message->setMessageBody($messageHTML);
$message->send();

?>